﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class DailyInvLocSkytransDA : IDailyInvLocSkytransDA
    {
        private string CREATED_BY = "@CREATED_BY";
        private string IN_LOC = "@IN_LOC";
        private string INV_DATE = "@INV_DATE";
        private string INV_LOC = "@INV_LOC";
        private string OUT_LOC = "@OUT_LOC";
        private string OWNER_ID = "@OWNER_ID";
        private string SQL_CHECK_DAILY_INV_LOC_SKYTRANS_ID_UNIQUE = " SELECT COUNT(1) FROM @_@DAILY_INV_LOC_SKYTRANS WHERE ROWID = @ROWID ";
        private string SQL_DELETE_DAILY_INV_LOC_SKYTRANS = " DELETE FROM @_@DAILY_INV_LOC_SKYTRANS WHERE ROWID = @ROWID ";
        private string SQL_INSERT_DAILY_INV_LOC_SKYTRANS = " INSERT INTO @_@DAILY_INV_LOC_SKYTRANS ( WH_ID, OWNER_ID, VENDOR_ID, INV_DATE, IN_LOC, OUT_LOC, INV_LOC, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @OWNER_ID, @VENDOR_ID, @INV_DATE, @IN_LOC, @OUT_LOC, @INV_LOC, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_DAILY_INV_LOC_SKYTRANS = " SELECT  ROWID, WH_ID, OWNER_ID, VENDOR_ID, INV_DATE, IN_LOC, OUT_LOC, INV_LOC, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@DAILY_INV_LOC_SKYTRANS ";
        private string SQL_UPDATE_DAILY_INV_LOC_SKYTRANS = " UPDATE @_@DAILY_INV_LOC_SKYTRANS SET WH_ID = @WH_ID, OWNER_ID = @OWNER_ID, VENDOR_ID = @VENDOR_ID, INV_DATE = @INV_DATE, IN_LOC = @IN_LOC, OUT_LOC = @OUT_LOC, INV_LOC = @INV_LOC, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ROWID = @ROWID  ";
        private string TS = "@TS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VENDOR_ID = "@VENDOR_ID";
        private string WH_ID = "@WH_ID";

        public bool CheckDailyInvLocSkytransIDUnique(string dailyInvLocSkytransID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", dailyInvLocSkytransID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_DAILY_INV_LOC_SKYTRANS_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckDailyInvLocSkytransIDUnique(DataBase dataBase, DbTransaction tran, string dailyInvLocSkytransID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", dailyInvLocSkytransID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_DAILY_INV_LOC_SKYTRANS_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteDailyInvLocSkytrans(List<string> dailyInvLocSkytransID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < dailyInvLocSkytransID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", dailyInvLocSkytransID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteDailyInvLocSkytrans(DataBase dataBase, DbTransaction tran, List<string> dailyInvLocSkytransID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < dailyInvLocSkytransID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", dailyInvLocSkytransID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllDailyInvLocSkytrans(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllDailyInvLocSkytrans(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public DailyInvLocSkytransInfo GetDailyInvLocSkytransByID(string dailyinvlocskytransID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID) + " WHERE ROWID = @ROWID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", dailyinvlocskytransID) };
            DailyInvLocSkytransInfo dailyInvLocSkytransInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    dailyInvLocSkytransInfo = new DailyInvLocSkytransInfo((reader["ROWID"].ToString() != "") ? decimal.Parse(reader["ROWID"].ToString()) : 0M, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["VENDOR_ID"].ToString(), (reader["INV_DATE"].ToString() != "") ? DateTime.Parse(reader["INV_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["IN_LOC"].ToString() != "") ? int.Parse(reader["IN_LOC"].ToString()) : 0, (reader["OUT_LOC"].ToString() != "") ? int.Parse(reader["OUT_LOC"].ToString()) : 0, (reader["INV_LOC"].ToString() != "") ? int.Parse(reader["INV_LOC"].ToString()) : 0, (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return dailyInvLocSkytransInfo;
        }

        public DailyInvLocSkytransInfo GetDailyInvLocSkytransByID(DataBase dataBase, DbTransaction tran, string dailyinvlocskytransID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID) + " WHERE ROWID = @ROWID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", dailyinvlocskytransID) };
            DailyInvLocSkytransInfo dailyInvLocSkytransInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                dailyInvLocSkytransInfo = new DailyInvLocSkytransInfo((reader["ROWID"].ToString() != "") ? decimal.Parse(reader["ROWID"].ToString()) : 0M, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["VENDOR_ID"].ToString(), (reader["INV_DATE"].ToString() != "") ? DateTime.Parse(reader["INV_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["IN_LOC"].ToString() != "") ? int.Parse(reader["IN_LOC"].ToString()) : 0, (reader["OUT_LOC"].ToString() != "") ? int.Parse(reader["OUT_LOC"].ToString()) : 0, (reader["INV_LOC"].ToString() != "") ? int.Parse(reader["INV_LOC"].ToString()) : 0, (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return dailyInvLocSkytransInfo;
        }

        public DataSet GetDailyInvLocSkytransByQueryList(List<string> sqlWhere, DailyInvLocSkytransQueryEntity dailyinvlocskytransQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!dailyinvlocskytransQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, dailyinvlocskytransQuery.CurrentPage, dailyinvlocskytransQuery.PageSize, dailyinvlocskytransQuery.SortField, dailyinvlocskytransQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetDailyInvLocSkytransByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, DailyInvLocSkytransQueryEntity dailyinvlocskytransQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, dailyinvlocskytransQuery.CurrentPage, dailyinvlocskytransQuery.PageSize, dailyinvlocskytransQuery.SortField, dailyinvlocskytransQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertDailyInvLocSkytrans(DailyInvLocSkytransInfo dailyInvLocSkytransInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    dailyInvLocSkytransInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DailyInvLocSkytrans_Parameters(dailyInvLocSkytransInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertDailyInvLocSkytrans(DataBase dataBase, DbTransaction tran, DailyInvLocSkytransInfo dailyInvLocSkytransInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    dailyInvLocSkytransInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DailyInvLocSkytrans_Parameters(dailyInvLocSkytransInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertDailyInvLocSkytransByOwner(DailyInvLocSkytransInfo dailyinvlocskytransInfo, List<string> whLoginID)
        {
            int result = 0;
            StringBuilder sbSQL = new StringBuilder();
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            sbSQL.AppendLine(" INSERT INTO @_@DAILY_INV_LOC_SKYTRANS (WH_ID,OWNER_ID,VENDOR_ID,INV_DATE,IN_LOC,OUT_LOC,INV_LOC,INV_LOC_DAMAGE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE) ");
            sbSQL.AppendLine(" SELECT @WH_ID, OWNER_ID, '', @INV_DATE, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 1 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_IN, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 2 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_OUT, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 3 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_INV, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 4 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_INV_DAMAGE, ");
            sbSQL.AppendLine("   @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() ");
            sbSQL.AppendLine(" FROM ( ");
            sbSQL.AppendLine(" SELECT 1 AS TRANS_TYPE, TMP.OWNER_ID, COUNT(DISTINCT TMP.TO_LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM ( ");
            sbSQL.AppendLine(" --先入库STAGE再移动上架入库 ");
            sbSQL.AppendLine(" SELECT IT_MOV.OWNER_ID, IT_MOV.TO_LOC ");
            sbSQL.AppendLine(" FROM @_@INV_TRANS IT_MOV ");
            sbSQL.AppendLine("   LEFT JOIN @_@INV_TRANS IT_IN ON IT_IN.OWNER_ID = IT_MOV.OWNER_ID AND IT_IN.SKU_ID = IT_MOV.SKU_ID AND IT_IN.LOT = IT_MOV.LOT AND IT_IN.TO_LPN_ID = IT_MOV.TO_LPN_ID AND IT_IN.TO_LOC = IT_MOV.FROM_LOC ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = IT_MOV.OWNER_ID AND LA.SKU_ID = IT_MOV.SKU_ID AND LA.LOT = IT_MOV.LOT ");
            sbSQL.AppendLine(" WHERE IT_IN.TRANS_TYPE = 'IN' AND IT_IN.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT_IN.[STATUS] = 'OK' AND IT_IN.TO_LOC = 'STAGE' ");
            sbSQL.AppendLine("   AND IT_MOV.TRANS_TYPE = 'MV' AND IT_MOV.SOURCE_TRANS_TYPE LIKE '%MOVE%' AND IT_MOV.[STATUS] = 'OK' ");
            sbSQL.AppendLine("   AND IT_MOV.TRANS_DATE >= @BEGIN_DATE AND IT_MOV.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --直接上架入库 ");
            sbSQL.AppendLine(" SELECT IT.OWNER_ID, IT.TO_LOC ");
            sbSQL.AppendLine(" FROM @_@INV_TRANS IT ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = IT.OWNER_ID AND LA.SKU_ID = IT.SKU_ID AND LA.LOT = IT.LOT ");
            sbSQL.AppendLine(" WHERE IT.TRANS_TYPE = 'IN' AND IT.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT.[STATUS] = 'OK' AND IT.TO_LOC <> 'STAGE' ");
            sbSQL.AppendLine("   AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" ) TMP ");
            sbSQL.AppendLine(" GROUP BY TMP.OWNER_ID ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --出库 ");
            sbSQL.AppendLine(" SELECT 2 AS TRANS_TYPE, PD.OWNER_ID, COUNT(DISTINCT PD.LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM @_@PICK_DETAIL PD ");
            sbSQL.AppendLine("   LEFT JOIN @_@INV_TRANS IT ON IT.DOC_ID = PD.PICK_DETAIL_ID AND PD.OWNER_ID = IT.OWNER_ID AND PD.SKU_ID = IT.SKU_ID AND PD.LOT = IT.LOT ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = PD.OWNER_ID AND LA.SKU_ID = PD.SKU_ID AND LA.LOT = PD.LOT ");
            sbSQL.AppendLine(" WHERE IT.TRANS_TYPE = 'OT' AND IT.SOURCE_TRANS_TYPE = 'PICKINGUPDATE' AND IT.[STATUS] = 'OK' AND PD.LOC <> 'STAGE' ");
            sbSQL.AppendLine("   AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" GROUP BY PD.OWNER_ID ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --库存 ");
            sbSQL.AppendLine(" SELECT 3 AS TRANS_TYPE, ILLL.OWNER_ID, COUNT(DISTINCT ILLL.LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM @_@INV_LOT_LOC_LPN ILLL ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = ILLL.OWNER_ID AND LA.SKU_ID = ILLL.SKU_ID AND LA.LOT = ILLL.LOT ");
            sbSQL.AppendLine(" WHERE ILLL.QTY > 0 AND ILLL.LOC NOT IN ('STAGE', 'PICKTO') ");
            sbSQL.AppendLine(" GROUP BY ILLL.OWNER_ID ");
            sbSQL.AppendLine("  UNION  ");
            sbSQL.AppendLine("  --库存  ");
            sbSQL.AppendLine("  SELECT 4 AS TRANS_TYPE, ILLL.OWNER_ID, COUNT(DISTINCT ILLL.LOC) AS LOC_COUNT  ");
            sbSQL.AppendLine("  FROM @_@INV_LOT_LOC_LPN ILLL  ");
            sbSQL.AppendLine("    LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = ILLL.OWNER_ID AND LA.SKU_ID = ILLL.SKU_ID AND LA.LOT = ILLL.LOT  ");
            sbSQL.AppendLine("  WHERE ILLL.QTY > 0 AND ILLL.LOC NOT IN ('STAGE', 'PICKTO') ");
            sbSQL.AppendLine("    AND ILLL.LOC IN (SELECT illl.LOC FROM @_@INV_LOT_LOC_LPN illl LEFT JOIN wh1.LOT_ATTRIBUTE la ON la.LOT = illl.LOT ");
            sbSQL.AppendLine("     WHERE illl.QTY > 0 ");
            sbSQL.AppendLine("       AND illl.LOC NOT IN (SELECT DISTINCT illl.LOC FROM @_@INV_LOT_LOC_LPN illl LEFT JOIN @_@LOT_ATTRIBUTE la ON la.LOT = illl.LOT WHERE illl.QTY > 0 AND la.LOT_ATTR09 <> 'DAMAGE') ");
            sbSQL.AppendLine("     GROUP BY illl.LOC ");
            sbSQL.AppendLine("     HAVING MIN(DATEDIFF(DAY, la.RECEIPT_DATE, GETDATE())) > 5) ");
            sbSQL.AppendLine("  GROUP BY ILLL.OWNER_ID ");
            sbSQL.AppendLine(" ) LOC_COUNT ");
            sbSQL.AppendLine(" WHERE OWNER_ID = @OWNER_ID ");
            sbSQL.AppendLine(" GROUP BY OWNER_ID ");
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    string sSQL = "SELECT COUNT(*) FROM @_@DAILY_INV_LOC_SKYTRANS WHERE OWNER_ID = @OWNER_ID AND CONVERT(DATETIME, CONVERT(VARCHAR(10), INV_DATE, 20)) = @INV_DATE";
                    sSQL = sSQL.Replace("@_@", whLoginID[i]).Replace("@OWNER_ID", "'" + dailyinvlocskytransInfo.OwnerID + "'").Replace("@INV_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd") + "'");
                    if (int.Parse(DBHelper.ExecuteScalar(CommandType.Text, sSQL).ToString()) == 0)
                    {
                        sSQL = sbSQL.ToString().Replace("@_@", whLoginID[i]).Replace("@WH_ID", "'" + whLoginID[i].Substring(0, whLoginID[i].Length - 1) + "'").Replace("@INV_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@CREATED_BY", "'" + dailyinvlocskytransInfo.CreatedBy + "'").Replace("@UPDATED_BY", "'" + dailyinvlocskytransInfo.CreatedBy + "'").Replace("@BEGIN_DATE", "'" + dailyinvlocskytransInfo.InvDate.AddDays(-1.0).ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@END_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@OWNER_ID", "'" + dailyinvlocskytransInfo.OwnerID + "'");
                        result += db.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertDailyInvLocSkytransByOwner(DataBase dataBase, DbTransaction tran, DailyInvLocSkytransInfo dailyinvlocskytransInfo, List<string> whLoginID)
        {
            int result = 0;
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.AppendLine(" INSERT INTO @_@DAILY_INV_LOC_SKYTRANS (WH_ID,OWNER_ID,VENDOR_ID,INV_DATE,IN_LOC,OUT_LOC,INV_LOC,INV_LOC_DAMAGE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE) ");
            sbSQL.AppendLine(" SELECT @WH_ID, OWNER_ID, '', @INV_DATE, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 1 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_IN, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 2 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_OUT, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 3 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_INV, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 4 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_INV_DAMAGE, ");
            sbSQL.AppendLine("   @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() ");
            sbSQL.AppendLine(" FROM ( ");
            sbSQL.AppendLine(" SELECT 1 AS TRANS_TYPE, TMP.OWNER_ID, COUNT(DISTINCT TMP.TO_LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM ( ");
            sbSQL.AppendLine(" --先入库STAGE再移动上架入库 ");
            sbSQL.AppendLine(" SELECT IT_MOV.OWNER_ID, IT_MOV.TO_LOC ");
            sbSQL.AppendLine(" FROM @_@INV_TRANS IT_MOV ");
            sbSQL.AppendLine("   LEFT JOIN @_@INV_TRANS IT_IN ON IT_IN.OWNER_ID = IT_MOV.OWNER_ID AND IT_IN.SKU_ID = IT_MOV.SKU_ID AND IT_IN.LOT = IT_MOV.LOT AND IT_IN.TO_LPN_ID = IT_MOV.TO_LPN_ID AND IT_IN.TO_LOC = IT_MOV.FROM_LOC ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = IT_MOV.OWNER_ID AND LA.SKU_ID = IT_MOV.SKU_ID AND LA.LOT = IT_MOV.LOT ");
            sbSQL.AppendLine(" WHERE IT_IN.TRANS_TYPE = 'IN' AND IT_IN.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT_IN.[STATUS] = 'OK' AND IT_IN.TO_LOC = 'STAGE' ");
            sbSQL.AppendLine("   AND IT_MOV.TRANS_TYPE = 'MV' AND IT_MOV.SOURCE_TRANS_TYPE LIKE '%MOVE%' AND IT_MOV.[STATUS] = 'OK' ");
            sbSQL.AppendLine("   AND IT_MOV.TRANS_DATE >= @BEGIN_DATE AND IT_MOV.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --直接上架入库 ");
            sbSQL.AppendLine(" SELECT IT.OWNER_ID, IT.TO_LOC ");
            sbSQL.AppendLine(" FROM @_@INV_TRANS IT ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = IT.OWNER_ID AND LA.SKU_ID = IT.SKU_ID AND LA.LOT = IT.LOT ");
            sbSQL.AppendLine(" WHERE IT.TRANS_TYPE = 'IN' AND IT.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT.[STATUS] = 'OK' AND IT.TO_LOC <> 'STAGE' ");
            sbSQL.AppendLine("   AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" ) TMP ");
            sbSQL.AppendLine(" GROUP BY TMP.OWNER_ID ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --出库 ");
            sbSQL.AppendLine(" SELECT 2 AS TRANS_TYPE, PD.OWNER_ID, COUNT(DISTINCT PD.LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM @_@PICK_DETAIL PD ");
            sbSQL.AppendLine("   LEFT JOIN @_@INV_TRANS IT ON IT.DOC_ID = PD.PICK_DETAIL_ID AND PD.OWNER_ID = IT.OWNER_ID AND PD.SKU_ID = IT.SKU_ID AND PD.LOT = IT.LOT ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = PD.OWNER_ID AND LA.SKU_ID = PD.SKU_ID AND LA.LOT = PD.LOT ");
            sbSQL.AppendLine(" WHERE IT.TRANS_TYPE = 'OT' AND IT.SOURCE_TRANS_TYPE = 'PICKINGUPDATE' AND IT.[STATUS] = 'OK' AND PD.LOC <> 'STAGE' ");
            sbSQL.AppendLine("   AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" GROUP BY PD.OWNER_ID ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --库存 ");
            sbSQL.AppendLine(" SELECT 3 AS TRANS_TYPE, ILLL.OWNER_ID, COUNT(DISTINCT ILLL.LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM @_@INV_LOT_LOC_LPN ILLL ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = ILLL.OWNER_ID AND LA.SKU_ID = ILLL.SKU_ID AND LA.LOT = ILLL.LOT ");
            sbSQL.AppendLine(" WHERE ILLL.QTY > 0 AND ILLL.LOC NOT IN ('STAGE', 'PICKTO') ");
            sbSQL.AppendLine(" GROUP BY ILLL.OWNER_ID ");
            sbSQL.AppendLine("  UNION  ");
            sbSQL.AppendLine("  --库存  ");
            sbSQL.AppendLine("  SELECT 4 AS TRANS_TYPE, ILLL.OWNER_ID, COUNT(DISTINCT ILLL.LOC) AS LOC_COUNT  ");
            sbSQL.AppendLine("  FROM @_@INV_LOT_LOC_LPN ILLL  ");
            sbSQL.AppendLine("    LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = ILLL.OWNER_ID AND LA.SKU_ID = ILLL.SKU_ID AND LA.LOT = ILLL.LOT  ");
            sbSQL.AppendLine("  WHERE ILLL.QTY > 0 AND ILLL.LOC NOT IN ('STAGE', 'PICKTO') ");
            sbSQL.AppendLine("    AND ILLL.LOC IN (SELECT illl.LOC FROM @_@INV_LOT_LOC_LPN illl LEFT JOIN wh1.LOT_ATTRIBUTE la ON la.LOT = illl.LOT ");
            sbSQL.AppendLine("     WHERE illl.QTY > 0 ");
            sbSQL.AppendLine("       AND illl.LOC NOT IN (SELECT DISTINCT illl.LOC FROM @_@INV_LOT_LOC_LPN illl LEFT JOIN @_@LOT_ATTRIBUTE la ON la.LOT = illl.LOT WHERE illl.QTY > 0 AND la.LOT_ATTR09 <> 'DAMAGE') ");
            sbSQL.AppendLine("     GROUP BY illl.LOC ");
            sbSQL.AppendLine("     HAVING MIN(DATEDIFF(DAY, la.RECEIPT_DATE, GETDATE())) > 5) ");
            sbSQL.AppendLine("  GROUP BY ILLL.OWNER_ID ");
            sbSQL.AppendLine(" ) LOC_COUNT ");
            sbSQL.AppendLine(" WHERE OWNER_ID = @OWNER_ID ");
            sbSQL.AppendLine(" GROUP BY OWNER_ID ");
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    string sSQL = "SELECT COUNT(*) FROM @_@DAILY_INV_LOC_SKYTRANS WHERE OWNER_ID = @OWNER_ID AND CONVERT(DATETIME, CONVERT(VARCHAR(10), INV_DATE, 20)) = @INV_DATE";
                    sSQL = sSQL.Replace("@_@", whLoginID[i]).Replace("@OWNER_ID", "'" + dailyinvlocskytransInfo.OwnerID + "'").Replace("@INV_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd") + "'");
                    if (int.Parse(dataBase.ExecuteScalar(tran, CommandType.Text, sSQL).ToString()) == 0)
                    {
                        sSQL = sbSQL.ToString().Replace("@_@", whLoginID[i]).Replace("@WH_ID", "'" + whLoginID[i].Substring(0, whLoginID[i].Length - 1) + "'").Replace("@INV_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@CREATED_BY", "'" + dailyinvlocskytransInfo.CreatedBy + "'").Replace("@UPDATED_BY", "'" + dailyinvlocskytransInfo.CreatedBy + "'").Replace("@BEGIN_DATE", "'" + dailyinvlocskytransInfo.InvDate.AddDays(-1.0).ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@END_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@OWNER_ID", "'" + dailyinvlocskytransInfo.OwnerID + "'");
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertDailyInvLocSkytransByVendor(DailyInvLocSkytransInfo dailyinvlocskytransInfo, List<string> whLoginID)
        {
            int result = 0;
            StringBuilder sbSQL = new StringBuilder();
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            sbSQL.AppendLine(" INSERT INTO @_@DAILY_INV_LOC_SKYTRANS (WH_ID,OWNER_ID,VENDOR_ID,INV_DATE,IN_LOC,OUT_LOC,INV_LOC,INV_LOC_DAMAGE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE) ");
            sbSQL.AppendLine(" SELECT @WH_ID, '', VENDOR_ID, @INV_DATE, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 1 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_IN, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 2 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_OUT, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 3 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_INV, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 4 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_INV_DAMAGE, ");
            sbSQL.AppendLine("   @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() ");
            sbSQL.AppendLine(" FROM ( ");
            sbSQL.AppendLine(" SELECT 1 AS TRANS_TYPE, TMP.VENDOR_ID, COUNT(DISTINCT TMP.TO_LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM ( ");
            sbSQL.AppendLine(" --先入库STAGE再移动上架入库 ");
            sbSQL.AppendLine(" SELECT LA.VENDOR_ID, IT_MOV.TO_LOC ");
            sbSQL.AppendLine(" FROM @_@INV_TRANS IT_MOV ");
            sbSQL.AppendLine("   LEFT JOIN @_@INV_TRANS IT_IN ON IT_IN.OWNER_ID = IT_MOV.OWNER_ID AND IT_IN.SKU_ID = IT_MOV.SKU_ID AND IT_IN.LOT = IT_MOV.LOT AND IT_IN.TO_LPN_ID = IT_MOV.TO_LPN_ID AND IT_IN.TO_LOC = IT_MOV.FROM_LOC ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = IT_MOV.OWNER_ID AND LA.SKU_ID = IT_MOV.SKU_ID AND LA.LOT = IT_MOV.LOT ");
            sbSQL.AppendLine(" WHERE IT_IN.TRANS_TYPE = 'IN' AND IT_IN.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT_IN.[STATUS] = 'OK' AND IT_IN.TO_LOC = 'STAGE' ");
            sbSQL.AppendLine("   AND IT_MOV.TRANS_TYPE = 'MV' AND IT_MOV.SOURCE_TRANS_TYPE LIKE '%MOVE%' AND IT_MOV.[STATUS] = 'OK' ");
            sbSQL.AppendLine("   AND IT_MOV.TRANS_DATE >= @BEGIN_DATE AND IT_MOV.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --直接上架入库 ");
            sbSQL.AppendLine(" SELECT LA.VENDOR_ID, IT.TO_LOC ");
            sbSQL.AppendLine(" FROM @_@INV_TRANS IT ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = IT.OWNER_ID AND LA.SKU_ID = IT.SKU_ID AND LA.LOT = IT.LOT ");
            sbSQL.AppendLine(" WHERE IT.TRANS_TYPE = 'IN' AND IT.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT.[STATUS] = 'OK' AND IT.TO_LOC <> 'STAGE' ");
            sbSQL.AppendLine("   AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" ) TMP ");
            sbSQL.AppendLine(" GROUP BY TMP.VENDOR_ID ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --出库 ");
            sbSQL.AppendLine(" SELECT 2 AS TRANS_TYPE,  LA.VENDOR_ID, COUNT(DISTINCT PD.LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM @_@PICK_DETAIL PD ");
            sbSQL.AppendLine("   LEFT JOIN @_@INV_TRANS IT ON IT.DOC_ID = PD.PICK_DETAIL_ID AND PD.OWNER_ID = IT.OWNER_ID AND PD.SKU_ID = IT.SKU_ID AND PD.LOT = IT.LOT ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = PD.OWNER_ID AND LA.SKU_ID = PD.SKU_ID AND LA.LOT = PD.LOT ");
            sbSQL.AppendLine(" WHERE IT.TRANS_TYPE = 'OT' AND IT.SOURCE_TRANS_TYPE = 'PICKINGUPDATE' AND IT.[STATUS] = 'OK' AND PD.LOC <> 'STAGE' ");
            sbSQL.AppendLine("   AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" GROUP BY  LA.VENDOR_ID ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --库存 ");
            sbSQL.AppendLine(" SELECT 3 AS TRANS_TYPE, LA.VENDOR_ID, COUNT(DISTINCT ILLL.LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM @_@INV_LOT_LOC_LPN ILLL ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = ILLL.OWNER_ID AND LA.SKU_ID = ILLL.SKU_ID AND LA.LOT = ILLL.LOT ");
            sbSQL.AppendLine(" WHERE ILLL.QTY > 0 AND ILLL.LOC NOT IN ('STAGE', 'PICKTO') ");
            sbSQL.AppendLine(" GROUP BY LA.VENDOR_ID ");
            sbSQL.AppendLine("  UNION  ");
            sbSQL.AppendLine("  --库存  ");
            sbSQL.AppendLine("  SELECT 4 AS TRANS_TYPE, LA.VENDOR_ID, COUNT(DISTINCT ILLL.LOC) AS LOC_COUNT  ");
            sbSQL.AppendLine("  FROM @_@INV_LOT_LOC_LPN ILLL  ");
            sbSQL.AppendLine("    LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = ILLL.OWNER_ID AND LA.SKU_ID = ILLL.SKU_ID AND LA.LOT = ILLL.LOT  ");
            sbSQL.AppendLine("  WHERE ILLL.QTY > 0 AND ILLL.LOC NOT IN ('STAGE', 'PICKTO') ");
            sbSQL.AppendLine("    AND ILLL.LOC IN (SELECT illl.LOC FROM @_@INV_LOT_LOC_LPN illl LEFT JOIN wh1.LOT_ATTRIBUTE la ON la.LOT = illl.LOT ");
            sbSQL.AppendLine("     WHERE illl.QTY > 0 ");
            sbSQL.AppendLine("       AND illl.LOC NOT IN (SELECT DISTINCT illl.LOC FROM @_@INV_LOT_LOC_LPN illl LEFT JOIN @_@LOT_ATTRIBUTE la ON la.LOT = illl.LOT WHERE illl.QTY > 0 AND la.LOT_ATTR09 <> 'DAMAGE') ");
            sbSQL.AppendLine("     GROUP BY illl.LOC ");
            sbSQL.AppendLine("     HAVING MIN(DATEDIFF(DAY, la.RECEIPT_DATE, GETDATE())) > 5) ");
            sbSQL.AppendLine("  GROUP BY LA.VENDOR_ID ");
            sbSQL.AppendLine(" ) LOC_COUNT ");
            sbSQL.AppendLine(" WHERE VENDOR_ID = @VENDOR_ID ");
            sbSQL.AppendLine(" GROUP BY VENDOR_ID ");
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    string sSQL = "SELECT COUNT(*) FROM @_@DAILY_INV_LOC_SKYTRANS WHERE VENDOR_ID = @VENDOR_ID AND CONVERT(DATETIME, CONVERT(VARCHAR(10), INV_DATE, 20)) = @INV_DATE";
                    sSQL = sSQL.Replace("@_@", whLoginID[i]).Replace("@VENDOR_ID", "'" + dailyinvlocskytransInfo.VendorID + "'").Replace("@INV_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd") + "'");
                    if (int.Parse(DBHelper.ExecuteScalar(CommandType.Text, sSQL).ToString()) == 0)
                    {
                        sSQL = sbSQL.ToString().Replace("@_@", whLoginID[i]).Replace("@WH_ID", "'" + whLoginID[i].Substring(0, whLoginID[i].Length - 1) + "'").Replace("@INV_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@CREATED_BY", "'" + dailyinvlocskytransInfo.CreatedBy + "'").Replace("@UPDATED_BY", "'" + dailyinvlocskytransInfo.CreatedBy + "'").Replace("@BEGIN_DATE", "'" + dailyinvlocskytransInfo.InvDate.AddDays(-1.0).ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@END_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@VENDOR_ID", "'" + dailyinvlocskytransInfo.VendorID + "'");
                        result += db.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertDailyInvLocSkytransByVendor(DataBase dataBase, DbTransaction tran, DailyInvLocSkytransInfo dailyinvlocskytransInfo, List<string> whLoginID)
        {
            int result = 0;
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.AppendLine(" INSERT INTO @_@DAILY_INV_LOC_SKYTRANS (WH_ID,OWNER_ID,VENDOR_ID,INV_DATE,IN_LOC,OUT_LOC,INV_LOC,INV_LOC_DAMAGE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE) ");
            sbSQL.AppendLine(" SELECT @WH_ID, '', VENDOR_ID, @INV_DATE, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 1 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_IN, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 2 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_OUT, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 3 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_INV, ");
            sbSQL.AppendLine("   ISNULL(SUM(CASE TRANS_TYPE WHEN 4 THEN ISNULL(LOC_COUNT,0) END),0) AS LOC_INV_DAMAGE, ");
            sbSQL.AppendLine("   @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() ");
            sbSQL.AppendLine(" FROM ( ");
            sbSQL.AppendLine(" SELECT 1 AS TRANS_TYPE, TMP.VENDOR_ID, COUNT(DISTINCT TMP.TO_LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM ( ");
            sbSQL.AppendLine(" --先入库STAGE再移动上架入库 ");
            sbSQL.AppendLine(" SELECT LA.VENDOR_ID, IT_MOV.TO_LOC ");
            sbSQL.AppendLine(" FROM @_@INV_TRANS IT_MOV ");
            sbSQL.AppendLine("   LEFT JOIN @_@INV_TRANS IT_IN ON IT_IN.OWNER_ID = IT_MOV.OWNER_ID AND IT_IN.SKU_ID = IT_MOV.SKU_ID AND IT_IN.LOT = IT_MOV.LOT AND IT_IN.TO_LPN_ID = IT_MOV.TO_LPN_ID AND IT_IN.TO_LOC = IT_MOV.FROM_LOC ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = IT_MOV.OWNER_ID AND LA.SKU_ID = IT_MOV.SKU_ID AND LA.LOT = IT_MOV.LOT ");
            sbSQL.AppendLine(" WHERE IT_IN.TRANS_TYPE = 'IN' AND IT_IN.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT_IN.[STATUS] = 'OK' AND IT_IN.TO_LOC = 'STAGE' ");
            sbSQL.AppendLine("   AND IT_MOV.TRANS_TYPE = 'MV' AND IT_MOV.SOURCE_TRANS_TYPE LIKE '%MOVE%' AND IT_MOV.[STATUS] = 'OK' ");
            sbSQL.AppendLine("   AND IT_MOV.TRANS_DATE >= @BEGIN_DATE AND IT_MOV.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --直接上架入库 ");
            sbSQL.AppendLine(" SELECT LA.VENDOR_ID, IT.TO_LOC ");
            sbSQL.AppendLine(" FROM @_@INV_TRANS IT ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = IT.OWNER_ID AND LA.SKU_ID = IT.SKU_ID AND LA.LOT = IT.LOT ");
            sbSQL.AppendLine(" WHERE IT.TRANS_TYPE = 'IN' AND IT.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT.[STATUS] = 'OK' AND IT.TO_LOC <> 'STAGE' ");
            sbSQL.AppendLine("   AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" ) TMP ");
            sbSQL.AppendLine(" GROUP BY TMP.VENDOR_ID ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --出库 ");
            sbSQL.AppendLine(" SELECT 2 AS TRANS_TYPE,  LA.VENDOR_ID, COUNT(DISTINCT PD.LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM @_@PICK_DETAIL PD ");
            sbSQL.AppendLine("   LEFT JOIN @_@INV_TRANS IT ON IT.DOC_ID = PD.PICK_DETAIL_ID AND PD.OWNER_ID = IT.OWNER_ID AND PD.SKU_ID = IT.SKU_ID AND PD.LOT = IT.LOT ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = PD.OWNER_ID AND LA.SKU_ID = PD.SKU_ID AND LA.LOT = PD.LOT ");
            sbSQL.AppendLine(" WHERE IT.TRANS_TYPE = 'OT' AND IT.SOURCE_TRANS_TYPE = 'PICKINGUPDATE' AND IT.[STATUS] = 'OK' AND PD.LOC <> 'STAGE' ");
            sbSQL.AppendLine("   AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE < @END_DATE ");
            sbSQL.AppendLine(" GROUP BY  LA.VENDOR_ID ");
            sbSQL.AppendLine(" UNION ");
            sbSQL.AppendLine(" --库存 ");
            sbSQL.AppendLine(" SELECT 3 AS TRANS_TYPE, LA.VENDOR_ID, COUNT(DISTINCT ILLL.LOC) AS LOC_COUNT ");
            sbSQL.AppendLine(" FROM @_@INV_LOT_LOC_LPN ILLL ");
            sbSQL.AppendLine("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = ILLL.OWNER_ID AND LA.SKU_ID = ILLL.SKU_ID AND LA.LOT = ILLL.LOT ");
            sbSQL.AppendLine(" WHERE ILLL.QTY > 0 AND ILLL.LOC NOT IN ('STAGE', 'PICKTO') ");
            sbSQL.AppendLine(" GROUP BY LA.VENDOR_ID ");
            sbSQL.AppendLine("  UNION  ");
            sbSQL.AppendLine("  --库存  ");
            sbSQL.AppendLine("  SELECT 4 AS TRANS_TYPE, LA.VENDOR_ID, COUNT(DISTINCT ILLL.LOC) AS LOC_COUNT  ");
            sbSQL.AppendLine("  FROM @_@INV_LOT_LOC_LPN ILLL  ");
            sbSQL.AppendLine("    LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = ILLL.OWNER_ID AND LA.SKU_ID = ILLL.SKU_ID AND LA.LOT = ILLL.LOT  ");
            sbSQL.AppendLine("  WHERE ILLL.QTY > 0 AND ILLL.LOC NOT IN ('STAGE', 'PICKTO') ");
            sbSQL.AppendLine("    AND ILLL.LOC IN (SELECT illl.LOC FROM @_@INV_LOT_LOC_LPN illl LEFT JOIN wh1.LOT_ATTRIBUTE la ON la.LOT = illl.LOT ");
            sbSQL.AppendLine("     WHERE illl.QTY > 0 ");
            sbSQL.AppendLine("       AND illl.LOC NOT IN (SELECT DISTINCT illl.LOC FROM @_@INV_LOT_LOC_LPN illl LEFT JOIN @_@LOT_ATTRIBUTE la ON la.LOT = illl.LOT WHERE illl.QTY > 0 AND la.LOT_ATTR09 <> 'DAMAGE') ");
            sbSQL.AppendLine("     GROUP BY illl.LOC ");
            sbSQL.AppendLine("     HAVING MIN(DATEDIFF(DAY, la.RECEIPT_DATE, GETDATE())) > 5) ");
            sbSQL.AppendLine("  GROUP BY LA.VENDOR_ID ");
            sbSQL.AppendLine(" ) LOC_COUNT ");
            sbSQL.AppendLine(" WHERE VENDOR_ID = @VENDOR_ID ");
            sbSQL.AppendLine(" GROUP BY VENDOR_ID ");
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    string sSQL = "SELECT COUNT(*) FROM @_@DAILY_INV_LOC_SKYTRANS WHERE VENDOR_ID = @VENDOR_ID AND CONVERT(DATETIME, CONVERT(VARCHAR(10), INV_DATE, 20)) = @INV_DATE";
                    sSQL = sSQL.Replace("@_@", whLoginID[i]).Replace("@VENDOR_ID", "'" + dailyinvlocskytransInfo.VendorID + "'").Replace("@INV_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd") + "'");
                    if (int.Parse(dataBase.ExecuteScalar(tran, CommandType.Text, sSQL).ToString()) == 0)
                    {
                        sSQL = sbSQL.ToString().Replace("@_@", whLoginID[i]).Replace("@WH_ID", "'" + whLoginID[i].Substring(0, whLoginID[i].Length - 1) + "'").Replace("@INV_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@CREATED_BY", "'" + dailyinvlocskytransInfo.CreatedBy + "'").Replace("@UPDATED_BY", "'" + dailyinvlocskytransInfo.CreatedBy + "'").Replace("@BEGIN_DATE", "'" + dailyinvlocskytransInfo.InvDate.AddDays(-1.0).ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@END_DATE", "'" + dailyinvlocskytransInfo.InvDate.ToString("yyyy-MM-dd HH:mm:ss") + "'").Replace("@VENDOR_ID", "'" + dailyinvlocskytransInfo.VendorID + "'");
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_DailyInvLocSkytrans_Parameters(DailyInvLocSkytransInfo dailyInvLocSkytransInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { new SqlParameter(this.WH_ID, SqlDbType.VarChar, 50), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 50), new SqlParameter(this.INV_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.IN_LOC, SqlDbType.Int, 4), new SqlParameter(this.OUT_LOC, SqlDbType.Int, 4), new SqlParameter(this.INV_LOC, SqlDbType.Int, 4), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 50), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 50) };
            if (!string.IsNullOrEmpty(dailyInvLocSkytransInfo.WhID))
            {
                paramArray[0].Value = dailyInvLocSkytransInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(dailyInvLocSkytransInfo.OwnerID))
            {
                paramArray[1].Value = dailyInvLocSkytransInfo.OwnerID;
            }
            else
            {
                paramArray[1].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(dailyInvLocSkytransInfo.VendorID))
            {
                paramArray[2].Value = dailyInvLocSkytransInfo.VendorID;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            if (dailyInvLocSkytransInfo.InvDate != new DateTime())
            {
                paramArray[3].Value = dailyInvLocSkytransInfo.InvDate;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            paramArray[4].Value = dailyInvLocSkytransInfo.InLoc;
            paramArray[5].Value = dailyInvLocSkytransInfo.OutLoc;
            paramArray[6].Value = dailyInvLocSkytransInfo.InvLoc;
            if (!string.IsNullOrEmpty(dailyInvLocSkytransInfo.CreatedBy))
            {
                paramArray[7].Value = dailyInvLocSkytransInfo.CreatedBy;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(dailyInvLocSkytransInfo.UpdatedBy))
            {
                paramArray[8].Value = dailyInvLocSkytransInfo.UpdatedBy;
                return paramArray;
            }
            paramArray[8].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateDailyInvLocSkytrans(DailyInvLocSkytransInfo dailyInvLocSkytransInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    dailyInvLocSkytransInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DailyInvLocSkytrans_Parameters(dailyInvLocSkytransInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateDailyInvLocSkytrans(DataBase dataBase, DbTransaction tran, DailyInvLocSkytransInfo dailyInvLocSkytransInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    dailyInvLocSkytransInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DailyInvLocSkytrans_Parameters(dailyInvLocSkytransInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_DAILY_INV_LOC_SKYTRANS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

